package com.example.project.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.project.entity.Issue;

/**
 * 问题，存储项目中的任务、缺陷等问题Mapper接口
 * 
 * @author ruoyi
 * @date 2025-09-19
 */
@Mapper
public interface IssueMapper {

	/**
	 * 查询问题，存储项目中的任务、缺陷等问题
	 * 
	 * @param id 问题，存储项目中的任务、缺陷等问题主键
	 * @return 问题，存储项目中的任务、缺陷等问题
	 */
	@Select("select id, tracker_id, project_id, subject, description, start_date, due_date, estimated_hours, category_id, status_id, assigned_to_id, "
			+ "priority_id, fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, "
			+ "del_flag, create_by, create_time, update_by, update_time from t_issue where id = #{id}")
	public Issue selectIssuesById(Long id);

	/**
	 * 查询问题，存储项目中的任务、缺陷等问题列表
	 * 
	 * @param tIssues 问题，存储项目中的任务、缺陷等问题
	 * @return 问题，存储项目中的任务、缺陷等问题集合
	 */
	@Select({ "<script>",
			"select id, tracker_id, project_id, subject, description, due_date, category_id, status_id, assigned_to_id, priority_id, ",
			"fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, ",
			"del_flag, create_by, create_time, update_by, update_time from t_issue where del_flag = 0",
			"<if test=\"issue.trackerId != null\"> and tracker_id = #{issue.trackerId}</if>",
			"<if test=\"issue.projectId != null\"> and project_id = #{issue.projectId}</if>",
			"<if test=\"issue.subject != null and issue.subject != ''\"> and subject = #{issue.subject}</if>",
			"<if test=\"issue.description != null and issue.description != ''\"> and description = #{issue.description}</if>",
			"<if test=\"issue.startDate != null\"> and start_date = #{issue.startDate}</if>",
			"<if test=\"issue.dueDate != null\"> and due_date = #{issue.dueDate}</if>",
			"<if test=\"issue.estimatedHours != null\"> and estimated_hours = #{issue.estimatedHours}</if>",
			"<if test=\"issue.categoryId != null\"> and category_id = #{issue.categoryId}</if>",
			"<if test=\"issue.statusId != null and issue.statusId != 0\"> and status_id = #{issue.statusId}</if>",
			"<if test=\"issue.statusId == 0\"> and status_id != 6</if>",
			"<if test=\"issue.assignedToId != null\"> and assigned_to_id = #{issue.assignedToId}</if>",
			"<if test=\"issue.priorityId != null\"> and priority_id = #{issue.priorityId}</if>",
			"<if test=\"issue.fixedVersionId != null\"> and fixed_version_id = #{issue.fixedVersionId}</if>",
			"<if test=\"issue.authorId != null\"> and author_id = #{issue.authorId}</if>",
			"<if test=\"issue.lockVersion != null\"> and lock_version = #{issue.lockVersion} </if>",
			"<if test=\"issue.recipient != 0 and issue.recipient != null\"> and recipient = #{issue.recipient} </if>",
			"<if test=\"issue.sDate != null and issue.sDate != '' and issue.eDate != null and issue.eDate != ''\"> and DATE(create_time) BETWEEN #{issue.sDate} AND #{issue.eDate} </if>",
			"${issue.params.dataScope}", "</script>" })
	public List<Issue> selectIssuesList(Page<Issue> page, @Param("issue") Issue issue);

	@Select({ "<script>",
			"select id, tracker_id, project_id, subject, description, due_date, category_id, status_id, assigned_to_id, priority_id, ",
			"fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, "
			+ "del_flag, create_by, create_time, update_by, update_time from t_issue where del_flag = 0",
			"<if test=\"issue.trackerId != null\"> and tracker_id = #{issue.trackerId}</if>",
			"<if test=\"issue.projectId != null\"> and project_id = #{issue.projectId}</if>",
			"<if test=\"issue.subject != null and issue.subject != ''\"> and subject = #{issue.subject}</if>",
			"<if test=\"issue.description != null and issue.description != ''\"> and description = #{issue.description}</if>",
			"<if test=\"issue.startDate != null\"> and start_date = #{issue.startDate}</if>",
			"<if test=\"issue.dueDate != null\"> and due_date = #{issue.dueDate}</if>",
			"<if test=\"issue.estimatedHours != null\"> and estimated_hours = #{issue.estimatedHours}</if>",
			"<if test=\"issue.categoryId != null\"> and category_id = #{issue.categoryId}</if>",
			"<if test=\"issue.statusId != null and issue.statusId != 0\"> and status_id = #{issue.statusId}</if>",
			"<if test=\"issue.statusId == 0\"> and status_id != 6</if>",
			"<if test=\"issue.assignedToId != null\"> and assigned_to_id = #{issue.assignedToId}</if>",
			"<if test=\"issue.priorityId != null\"> and priority_id = #{issue.priorityId}</if>",
			"<if test=\"issue.fixedVersionId != null\"> and fixed_version_id = #{issue.fixedVersionId}</if>",
			"<if test=\"issue.authorId != null\"> and author_id = #{issue.authorId}</if>",
			"<if test=\"issue.lockVersion != null\"> and lock_version = #{issue.lockVersion} </if>",
			"<if test=\"issue.recipient != 0 and issue.recipient != null\"> and recipient = #{issue.recipient} </if>",
			"${issue.params.dataScope} ", 
	        "<if test='size != 0'> limit #{size} </if>","</script>" })
	public List<Issue> findListByConditionWithLimit(Issue issue, int size);

	@Select({ "<script>", "select count(1) from t_issue where del_flag = 0",
			"<if test=\"issue.trackerId != null\"> and tracker_id = #{issue.trackerId}</if>",
			"<if test=\"issue.projectId != null\"> and project_id = #{issue.projectId}</if>",
			"<if test=\"issue.subject != null and issue.subject != ''\"> and subject = #{issue.subject}</if>",
			"<if test=\"issue.description != null and issue.description != ''\"> and description = #{issue.description}</if>",
			"<if test=\"issue.startDate != null\"> and start_date = #{issue.startDate}</if>",
			"<if test=\"issue.dueDate != null\"> and due_date = #{issue.dueDate}</if>",
			"<if test=\"issue.estimatedHours != null\"> and estimated_hours = #{issue.estimatedHours}</if>",
			"<if test=\"issue.categoryId != null\"> and category_id = #{issue.categoryId}</if>",
			"<if test=\"issue.statusId != null and issue.statusId != 0\"> and status_id = #{issue.statusId}</if>",
			"<if test=\"issue.statusId == 0\"> and status_id != 5</if>",
			"<if test=\"issue.assignedToId != null\"> and assigned_to_id = #{issue.assignedToId}</if>",
			"<if test=\"issue.priorityId != null\"> and priority_id = #{issue.priorityId}</if>",
			"<if test=\"issue.fixedVersionId != null\"> and fixed_version_id = #{issue.fixedVersionId}</if>",
			"<if test=\"issue.authorId != null\"> and author_id = #{issue.authorId}</if>",
			"<if test=\"issue.lockVersion != null\"> and lock_version = #{issue.lockVersion} </if>",
			"<if test=\"issue.recipient != 0 and issue.recipient != null\"> and recipient = #{issue.recipient} </if>",
			"${issue.params.dataScope}", "</script>" })
	public long getCount(@Param("issue") Issue issue);

	/**
	 * 查询当日需求量（按当前日期查询startDate字段）
	 * 
	 * @return 当日需求量
	 */
	@Select("SELECT COUNT(1) FROM t_issue WHERE del_flag = 0 AND DATE(create_time) = CURDATE()")
	public long getTodayCount();

	/**
	 * 新增问题，存储项目中的任务、缺陷等问题
	 * 
	 * @param tIssues 问题，存储项目中的任务、缺陷等问题
	 * @return 结果
	 */
	@Insert({ "<script>", "insert into t_issue", "<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">",
			"<if test=\"trackerId != null\">tracker_id,</if>", "<if test=\"projectId != null\">project_id,</if>",
			"<if test=\"subject != null and subject != ''\">subject,</if>",
			"<if test=\"description != null\">description,</if>", "<if test=\"startDate != null\">start_date,</if>",
			"<if test=\"dueDate != null\">due_date,</if>", "<if test=\"estimatedHours != null\">estimated_hours,</if>",
			"<if test=\"categoryId != null\">category_id,</if>", "<if test=\"statusId != null\">status_id,</if>",
			"<if test=\"assignedToId != null\">assigned_to_id,</if>",
			"<if test=\"priorityId != null\">priority_id,</if>",
			"<if test=\"fixedVersionId != null\">fixed_version_id,</if>",
			"<if test=\"authorId != null\">author_id,</if>", "<if test=\"lockVersion != null\">lock_version,</if>",
			"<if test=\"doneRatio != null\">done_ratio,</if>", "<if test=\"recipient != null\">#{recipient},</if>",
			"<if test=\"delFlag != null\">del_flag,</if>", "<if test=\"createBy != null\">create_by,</if>",
			"<if test=\"createTime != null\">create_time,</if>", "<if test=\"updateBy != null\">update_by,</if>",
			"<if test=\"updateTime != null\">update_time,</if>", "</trim>",
			"<trim prefix=\"values (\" suffix=\")\" suffixOverrides=\",\">",
			"<if test=\"trackerId != null\">#{trackerId},</if>", "<if test=\"projectId != null\">#{projectId},</if>",
			"<if test=\"subject != null and subject != ''\">#{subject},</if>",
			"<if test=\"description != null\">#{description},</if>",
			"<if test=\"startDate != null\">#{startDate},</if>", "<if test=\"dueDate != null\">#{dueDate},</if>",
			"<if test=\"estimatedHours != null\">#{estimatedHours},</if>",
			"<if test=\"categoryId != null\">#{categoryId},</if>", "<if test=\"statusId != null\">#{statusId},</if>",
			"<if test=\"assignedToId != null\">#{assignedToId},</if>",
			"<if test=\"priorityId != null\">#{priorityId},</if>",
			"<if test=\"fixedVersionId != null\">#{fixedVersionId},</if>",
			"<if test=\"authorId != null\">#{authorId},</if>", "<if test=\"lockVersion != null\">#{lockVersion},</if>",
			"<if test=\"doneRatio != null\">#{doneRatio},</if>", "<if test=\"recipient != null\">#{recipient},</if>",
			"<if test=\"delFlag != null\">#{delFlag},</if>", "<if test=\"createBy != null\">#{createBy},</if>",
			"<if test=\"createTime != null\">#{createTime},</if>", "<if test=\"updateBy != null\">#{updateBy},</if>",
			"<if test=\"updateTime != null\">#{updateTime},</if>", "</trim>", "</script>" })
	@Options(useGeneratedKeys = true, keyProperty = "id")
	public long insertIssues(Issue tIssues);

	/**
	 * 修改问题，存储项目中的任务、缺陷等问题
	 * 
	 * @param tIssues 问题，存储项目中的任务、缺陷等问题
	 * @return 结果
	 */
	@Update({ "<script>", "update t_issue", "<trim prefix=\"SET\" suffixOverrides=\",\">",
			"<if test=\"trackerId != null\">tracker_id = #{trackerId},</if>",
			"<if test=\"projectId != null\">project_id = #{projectId},</if>",
			"<if test=\"subject != null and subject != ''\">subject = #{subject},</if>",
			"<if test=\"description != null\">description = #{description},</if>",
			"<if test=\"startDate != null\">start_date = #{startDate},</if>",
			"<if test=\"dueDate != null\">due_date = #{dueDate},</if>",
			"<if test=\"estimatedHours != null\">estimated_hours = #{estimatedHours},</if>",
			"<if test=\"categoryId != null\">category_id = #{categoryId},</if>",
			"<if test=\"statusId != null\">status_id = #{statusId},</if>",
			"<if test=\"assignedToId != null\">assigned_to_id = #{assignedToId},</if>",
			"<if test=\"priorityId != null\">priority_id = #{priorityId},</if>",
			"<if test=\"fixedVersionId != null\">fixed_version_id = #{fixedVersionId},</if>",
			"<if test=\"lockVersion != null\">lock_version = #{lockVersion},</if>",
            "<if test=\"doneRatio != null\">done_ratio = #{doneRatio},</if>",
            "<if test=\"recipient != null\">recipient = #{recipient},</if>",
            "<if test=\"collectionTime != null\">collection_time = #{collectionTime},</if>",
            "<if test=\"completionTime != null\">completion_time = #{completionTime},</if>",
			"<if test=\"updateBy != null\">update_by = #{updateBy},</if>",
			"<if test=\"updateTime != null\">update_time = #{updateTime},</if>", "</trim>", "where id = #{id}",
			"</script>" })
	public int updateIssues(Issue tIssues);

	/**
	 * 删除问题，存储项目中的任务、缺陷等问题
	 * 
	 * @param id 问题，存储项目中的任务、缺陷等问题主键
	 * @return 结果
	 */
//	@Delete("delete from t_issue where id = #{id}")
	@Update("update t_issue set del_flag = 1, update_time = NOW() where id = #{id}")
	public int deleteIssuesById(Long id);

	/**
	 * 批量删除问题，存储项目中的任务、缺陷等问题
	 * 
	 * @param ids 需要删除的数据主键集合
	 * @return 结果
	 */
//	@Delete({ "<script>", "delete from t_issue where id in",
//			"<foreach item=\"id\" collection=\"array\" open=\"(\" separator=\",\" close=\")\">", "#{id}", "</foreach>",
//			"</script>" })
	@Update("<script>" + "update t_issue set del_flag = 1, update_time = NOW() where id in "
			+ "<foreach item='id' collection='array' open='(' separator=',' close=')'>" + "#{id}" + "</foreach>"
			+ "</script>")
	public int deleteIssuesByIds(String[] ids);

	@Select("select id, tracker_id, project_id, subject, description, start_date, due_date, estimated_hours, category_id, status_id, assigned_to_id, "
			+ "priority_id, fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, "
			+ "del_flag, create_by, create_time, update_by, update_time from t_issue "
			+ "where del_flag = 0 and assigned_to_id = #{userId} order by update_by desc")
	public List<Issue> findListByAssignedToId(Long userId);

	@Select("select * from (SELECT i.project_id, CASE WHEN p.NAME != '' THEN p.NAME ELSE p.customer_name END AS project_name, p.identifier, COUNT( 1 ) AS issue_count "
			+ "FROM t_issue i INNER JOIN t_project p ON i.project_id = p.id GROUP BY i.project_id) t order by issue_count desc limit 10")
	public List<Map<String, Object>> findTotalGroupByProjectId();

	@Select("SELECT DATE_FORMAT(create_time, '%m') AS month, COUNT(*) AS total FROM t_issue "
			+ "WHERE DATE_FORMAT(create_time, '%Y') = DATE_FORMAT(CURDATE(), '%Y') "
			+ "GROUP BY DATE_FORMAT(create_time, '%m') ORDER BY month")
	public List<Map<String, Object>> findListGroupByMonth();

	/**
	 * 根据状态统计问题数量
	 * 
	 * @param statusId 问题状态ID
	 * @return 问题数量
	 */
	@Select("select count(1) from t_issue where del_flag = 0 and status_id = #{statusId}")
	Integer selectCountByStatus(Long statusId);

	/**
	 * 获取项目问题分布统计
	 * 
	 * @return 分布统计列表
	 */
	@Select("SELECT p.name as project_name, COUNT(i.id) as issue_count " +
			"FROM t_project p LEFT JOIN t_issue i ON p.id = i.project_id AND i.del_flag = 0 " +
			"WHERE p.del_flag = 0 GROUP BY p.id, p.name ORDER BY issue_count DESC")
	List<Map<String, Object>> selectProjectIssueDistribution();

	/**
	 * 获取问题状态统计
	 * 
	 * @return 状态统计列表
	 */
	@Select("SELECT status_id, COUNT(1) as issue_count " +
			"FROM t_issue WHERE del_flag = 0 " +
			"GROUP BY status_id")
	List<Map<String, Object>> selectIssueStatusTotal();

	/**
	 * 获取问题优先级统计
	 * 
	 * @return 优先级统计列表
	 */
	@Select("SELECT priority_id, COUNT(1) as issue_count " +
			"FROM t_issue WHERE del_flag = 0 " +
			"GROUP BY priority_id")
	List<Map<String, Object>> selectIssuePriorityStats();

	@Select("select count(1) from t_issue where del_flag = 0 and assigned_to_id = 0")
	public Integer selectTotalByUnassigned();

	@Select("select project_id,status_id, count(1) as total from t_issue i where i.del_flag = 0 AND DATE(create_time) BETWEEN #{startDate} AND #{endDate} GROUP BY i.project_id, i.status_id")
	public List<Map<String, Object>> selectTotalGroupByProjectIdAndStatusId(String startDate, String endDate);

	/**
	 * 根据多个ID查询问题列表
	 * 
	 * @param ids 问题ID列表
	 * @return 问题列表
	 */
	@Select({
		"<script>",
		"select id, tracker_id, project_id, subject, description, start_date, due_date, estimated_hours, category_id, status_id, assigned_to_id, ",
		"priority_id, fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, ",
		"del_flag, create_by, create_time, update_by, update_time from t_issue where id in",
		"<foreach item='id' collection='list' open='(' separator=',' close=')'>",
		"#{id}",
		"</foreach>",
		"</script>"
	})
	List<Issue> selectIssuesByIds(@Param("ids") List<Long> ids);

	/**
	 * 查询可用于相似度计算的需求候选列表
	 * 
	 * @param issueId 当前需求ID
	 * @param projectId 项目ID
	 * @return 候选需求列表
	 */
	@Select({
		"select id, tracker_id, project_id, subject, description, start_date, due_date, estimated_hours, category_id, status_id, assigned_to_id, ",
		"priority_id, fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, ",
		"del_flag, create_by, create_time, update_by, update_time from t_issue ",
		"where del_flag = 0 and id != #{issueId} and project_id = #{projectId} ",
		"order by create_time desc limit 50"
	})
	List<Issue> selectCandidatesForSimilarity(@Param("issueId") Long issueId, @Param("projectId") Long projectId);

	/**
	 * 根据关键词查询需求列表
	 * 
	 * @param keywords 关键词列表
	 * @param projectId 项目ID（可为null）
	 * @return 需求列表
	 */
	@Select({
		"<script>",
		"select id, tracker_id, project_id, subject, description, start_date, due_date, estimated_hours, category_id, status_id, assigned_to_id, ",
		"priority_id, fixed_version_id, author_id, lock_version, done_ratio, recipient, collection_time, completion_time, ",
		"del_flag, create_by, create_time, update_by, update_time from t_issue where del_flag = 0 and (",
		"<foreach item='keyword' collection='keywords' open='' separator=' or ' close=''>",
		"subject like concat('%', #{keyword}, '%') or description like concat('%', #{keyword}, '%')",
		"</foreach>",
		")",
		"<if test='projectId != null'>",
		" and project_id = #{projectId}",
		"</if>",
		" order by create_time desc limit 20",
		"</script>"
	})
	List<Issue> selectByKeywords(@Param("keywords") List<String> keywords, @Param("projectId") Long projectId);

	/**
	 * 更新问题信息
	 * 
	 * @param issue 问题对象
	 * @return 更新结果
	 */
	@Update({
		"<script>",
		"update t_issue",
		"<trim prefix='SET' suffixOverrides=','>",
		"<if test='subject != null'>subject = #{subject},</if>",
		"<if test='description != null'>description = #{description},</if>",
		"<if test='categoryId != null'>category_id = #{categoryId},</if>",
		"<if test='statusId != null'>status_id = #{statusId},</if>",
		"<if test='assignedToId != null'>assigned_to_id = #{assignedToId},</if>",
		"<if test='priorityId != null'>priority_id = #{priorityId},</if>",
		"<if test='trackerId != null'>tracker_id = #{trackerId},</if>",
		"<if test='doneRatio != null'>done_ratio = #{doneRatio},</if>",
		"<if test='keywords != null'>keywords = #{keywords},</if>",
		"<if test='categoryTags != null'>category_tags = #{categoryTags},</if>",
		"update_time = now()",
		"</trim>",
		"where id = #{id}",
		"</script>"
	})
	int updateIssue(Issue issue);
}
